![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
How To Identify Badly Formed SQL StatementsBadly tuned SQL statements tend to access the database in a very inefficient way, causing unnecessary amounts of data to be scanned and transferred across the network. Badly tuned statements can cause a well-tuned server to expend large amounts of unnecessary processing power and I/O resources. You can identify badly tuned SQL statements with the Oracle EXPLAIN PLAN command and SQL Trace facility, as described in Chapter 25, Using EXPLAIN PLAN and SQL Trace. Some of the attributes of a badly tuned SQL statement are listed here:
These attributes should alert you to the fact that the SQL statements are not optimally tuned for the task being done. If your SQL statement exhibits any of these characteristics, you should make some correction to the statement. The remaining chapters in Part IV of this book, Tuning SQL, explain how to correct these problems. Transaction ProcessingBefore looking at specific SQL statements, this section analyzes how a transaction occurs in Oracle and then looks in more detail at how the SQL statement is actually parsed and the execution plan formed. Remember that a transaction is a logical group of work consisting of one or many SQL statements and ending with a commit or a rollback. In a typical transaction, the following steps are executed (see Figure 24.1):
While this process is occurring, the Oracle background processes are doing their jobs keeping the system running smoothly. Keep in mind that your application is being processed, hundreds of other users may be doing similar tasks. It is Oracles job to keep the system in a consistent state, managing contention and locking and performing at the necessary rate. Even though your application may have modified some data in the database, that data may not yet be written to the data files. It may be some time later that the DBWR process writes those changes out to permanent storage. With this overview of how the application is processed, you are ready to focus on what happens in step 2 of phase B: how the SQL statement is parsed and the execution plan is formed. SQL Statement ProcessingBy understanding how Oracle processes SQL statements, you can have a better understanding of how to optimize these statements. The following sections look at the SQL statement parsing process and how an execution plan is formed. For each SQL statement that is executed, several steps occur (see Figure 24.2):
Cursor CreationEach time an SQL statement is executed, a cursor is automatically created on behalf of the statement. If you want, you can declare the cursor manually. Remember that a cursor is a handle to a specific private SQL area. You can think of a cursor as a pointer to, or the name of, a particular area of memory associated with an SQL statement.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |